  --그래픽카드 속도 정렬 테이블
  drop table graphic_speed;
  
  create table graphic_speed(
  	no NUMBER, 
  	id varchar2(20) UNIQUE, 
  	constraint graphic_speed_fk foreign key(id) references product(id) on delete cascade
  );
  
  select * from graphic_speed order by no asc;
  select css.no, p.id, p.name from graphic_speed css, product p where css.ID = p.ID order by css.no asc;
  select count(no) from graphic_speed;
  
  delete from graphic_speed;
  

-- product 행 삭제시 graphic_speed 자동 정렬을 위한 트리거 생성
drop trigger delete_graphic;
	
  CREATE OR REPLACE TRIGGER delete_GRAPHIC
	AFTER DELETE ON product
	for each row
	WHEN(OLD.CATEGORY_ID='fc-7')
	DECLARE
		-- 자율 트랜잭션
		-- 부모 트랜잭션의 승인/롤백 여부에 상관없이 자신의 작업을 승인하거나 롤백할 수 있다.
		pragma autonomous_transaction;
		max_no number;
		del_no number;
	BEGIN
		if deleting then
			dbms_output.put_line('deleting');
			select max(no) into max_no from graphic_speed;
			select no into del_no from graphic_speed where id=:OLD.id;
			if max_no > del_no then
				update graphic_speed set no=no-1 where no > del_no;
				commit; --commit 구문 빠졌을시 trigger 오류 발생
			end if;
		end if;
	END;
	
-- product 행 추가시 graphic_speed 가장 마지막 no로 자동 등록을 수행하는 트리거 생성	
drop trigger insert_graphic;

  CREATE OR REPLACE TRIGGER insert_GRAPHIC
	AFTER INSERT ON product
	for each row
	WHEN(NEW.CATEGORY_ID='fc-7')
	BEGIN
		if inserting then
			dbms_output.put_line('inserting');
			insert into graphic_speed(no, id) values((select NVL(max(no+1), 1) from graphic_speed), :NEW.id);
		end if;
	END;
	
	
--VGA (저속→고속)  GT730 < AMD R7 < GT740 < GTX650 < R7 260X < GTX750 < GTX750Ti < GTX660 < R9 270X < GTX760 	

--insert_GRAPHIC 트리거 테스트--
  --graphic 속도별 검색을 위한 임시 상품등록
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GTX750', 98700, 'AMD','GTX750', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GTX650', 98700, 'AMD','GTX650', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','AMD R7', 98700, 'AMD','AMD R7', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GTX660', 98700, 'AMD','GTX660', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GT730', 98700, 'AMD','GT730', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GT740', 98700, 'AMD','GT740', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','R9 270X', 98700, 'AMD','R9 270X', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','R7 260X', 98700, 'AMD','R7 260X', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GTX750Ti', 98700, 'AMD','GTX750Ti', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','GTX760', 98700, 'AMD','GTX760', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));

select ps.no, p.id, p.name from graphic_speed ps, product p where ps.ID = p.ID order by ps.no asc;

--그래픽카드 속도별 정렬
update graphic_speed set no=1 where ID='pn-114';
update graphic_speed set no=2 where ID='pn-111';
update graphic_speed set no=3 where ID='pn-108';
update graphic_speed set no=4 where ID='pn-113';
update graphic_speed set no=5 where ID='pn-115';
update graphic_speed set no=6 where ID='pn-112';
update graphic_speed set no=7 where ID='pn-106';
update graphic_speed set no=8 where ID='pn-110';
update graphic_speed set no=9 where ID='pn-107';
update graphic_speed set no=10 where ID='pn-109';

select * from product;
select * from product where category_name='그래픽카드';


--delete_GRAPHIC 트리거 테스트--
delete from product where id='pn-105';
delete from product where id in (select id from product where CATEGORY_NAME='그래픽카드');
--delete from graphic_speed where id='pn-84';
select * from product where CATEGORY_NAME='그래픽카드';
select * from product;
select * from graphic_speed order by no asc;

--그래픽카드 속도별 부품 조회
select * from product p, graphic_speed ps where p.id=ps.id(+) order by ps.no asc;
select * from product p, graphic_speed ps where p.id=ps.id order by ps.no asc;

--graphic_speed 테이블을 기준으로한 완제품 등록
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_17', '17번 PC', 75000, 32, null, 'pn-113');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_18', '18번 PC', 75000, 32, null, 'pn-111');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_19', '19번 PC', 75000, 32, null, 'pn-115');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_20', '20번 PC', 75000, 32, null, 'pn-112');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_21', '21번 PC', 75000, 32, null, 'pn-108');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_22', '22번 PC', 75000, 32, null, 'pn-106');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_23', '23번 PC', 75000, 32, null, 'pn-114');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_24', '24번 PC', 75000, 32, null, 'pn-108');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_25', '25번 PC', 75000, 32, null, 'pn-107');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_26', '26번 PC', 75000, 32, null, 'pn-109');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_27', '27번 PC', 75000, 32, null, 'pn-109');
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_28', '28번 PC', 75000, 32, null, 'pn-110');

--완제품 전체조회
select complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id from complete_pc;

--그래픽카드 속도별 완제품 조회
select cp.complete_id, cp.complete_name, cp.complete_price, cp.complete_quantity, cp.cpu_id, cp.graphic_id, ps.no from complete_pc cp, graphic_speed ps where cp.graphic_id=ps.id(+) order by ps.no asc;


--CPU 속도별 완제품 조회 mapper 파일 작성
<select id="selectCompletePCByGRAPHIC" resultType="completePCResult">
   select	d.fileName, d.completeId, d.completeName, d.completePrice,
  				d.completeQuantity, d.mainboardName, d.cpuName,	d.coolerName,
  				d.memoryName, d.graphicName, d.hddName, d.ssdName, d.dvdName,
  				d.soundName, d.powerName, d.caseName, d.osName, d.cnt
  from	(<include refid="completePC-basic"/>) d, (select ps.no no, p.id id, p.name name from graphic_speed ps, product p where ps.ID = p.ID) p 
  where d.graphicName=p.name(+) order by p.no ASC
  </select>
